package db.tables;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;

import control.ConnectionManage;

import db.parking.beans.Complaints;

public class AdminComplaints {

	private static Connection conn = ConnectionManage.getInstance()
			.getConnection();

	/*
	 * public static void displayAllRows() throws SQLException {
	 * 
	 * String sql = "SELECT *  FROM Comlaints"; try ( Statement stmt =
	 * conn.createStatement(); ResultSet rs = stmt.executeQuery(sql); ){
	 * 
	 * System.out.println("Complaints Table:"); while (rs.next()) { StringBuffer
	 * bf = new StringBuffer(); bf.append(rs.getInt("id") + ": ");
	 * bf.append(rs.getString("username") +", ");
	 * bf.append(rs.getString("age")); System.out.println(bf.toString()); } } }
	 */
	public static Complaints getRow(int complaintId) throws SQLException {

		String sql = "SELECT * FROM Complaints WHERE oid = ?";
		ResultSet rs = null;

		try (PreparedStatement stmt = conn.prepareStatement(sql);) {
			stmt.setInt(1, complaintId);
			rs = stmt.executeQuery();

			if (rs.next()) {
				Complaints bean = new Complaints();
				bean.setComplaintID(complaintId);
				bean.setTimestamp(rs.getTimestamp("timestamp"));
				bean.setCustomerID(rs.getString("customerID"));
				bean.setDescription(rs.getString("description"));
				bean.setStatus(rs.getInt("status"));
				bean.setOrderID(rs.getInt("orderID"));
				bean.setAmount(rs.getInt("amount"));
				bean.setTitle(rs.getString("title"));

				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	public static boolean insert(Complaints bean) throws Exception {
		// complaintID is autoIncremented on Insert
		String sql = "INSERT into Complaints (timestamp , customerID , title , description , status , orderID , amount) "
				+ "VALUES (?, ?, ?, ?, ?, ?, ?)";
		ResultSet keys = null;
		try (PreparedStatement stmt = conn.prepareStatement(sql,
				Statement.RETURN_GENERATED_KEYS);) {

			stmt.setTimestamp(1, bean.getTimestamp());
			stmt.setString(2, bean.getCustomerID());
			stmt.setString(3, bean.getTitle());
			stmt.setString(4, bean.getDescription());
			stmt.setInt(5, bean.getStatus());
			stmt.setInt(6, bean.getOrderID());
			stmt.setInt(7, bean.getAmount());
			int affected = stmt.executeUpdate();

			if (affected == 1) {
				keys = stmt.getGeneratedKeys();
				keys.next();
				int newKey = keys.getInt(1);
				bean.setComplaintID(newKey);
			} else {
				System.err.println("No rows affected");
				return false;
			}

		} catch (SQLException e) {
			System.err.println(e + "AdminComplaint");
			return false;
		} finally {
			if (keys != null)
				keys.close();
		}
		return true;

	}

	// update complaint status
	public static boolean update(Complaints bean) throws Exception {

		String sql = "UPDATE Complaints SET " + "status = ? " + "WHERE id = ?";
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {

			stmt.setInt(1, bean.getStatus());
			stmt.setInt(2, bean.getComplaintID());

			int affected = stmt.executeUpdate();
			if (affected == 1) {
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return false;
		}

	}

	// delete complaint by id
	public static boolean delete(int complaintId) throws Exception {

		String sql = "DELETE FROM Complaints WHERE complaintID = ?";
		try (PreparedStatement stmt = conn.prepareStatement(sql);) {

			stmt.setInt(1, complaintId);
			int affected = stmt.executeUpdate();
			if (affected == 1) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		}

	}

}
